<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<HTML XMLNS="http://www.w3.org/1999/xhtml">
<HEAD>
<META HTTP-EQUIV="refresh" CONTENT="60" />
<TITLE>Dispatch Report Data</TITLE>
<LINK HREF="UniversalStyleSheet.css" REL="stylesheet" TYPE="text/css" />
<%
function nz(EvalStr,IsNullReturn)
	if isnull(EvalStr) then
		nz=IsNullReturn
	else
		nz=EvalStr
	end if
end function

function IIf(expr, truepart, falsepart)
	If expr Then
	   IIf = truepart
	Else
	   IIf = falsepart
	End If

end function

sortcol=request.QueryString("sort")
sortcol=replace(sortcol,"+"," ")
if sortcol="" then sortcol="created_time"
filterfield=request.QueryString("filterfield")
if filterfield=" " then filterfield=""
filtercriteria=replace(request.QueryString("filtercriteria"),"+"," ")
filtercriteria=trim(filtercriteria)
filtertype=request.QueryString("filtertype")

if left(trim(filtercriteria),2)="<>" then
	criteriaNot=True
	filtercriteria=right(filtercriteria,len(filtercriteria)-2)
	filtercriteria=trim(filtercriteria)
end if

if instr(filtercriteria,",")>0 then
	if criteriaNot then
		criteriaOperator = " not in("
	else
		criteriaOperator = " in("
	end if
	criteriaOperatorClose = ")"
else
	if criteriaNot then
		criteriaOperator = " <> "
	else
		criteriaOperator = " = "
	end if
	criteriaOperatorClose = ""
end if

if filterfield <> "" and filtercriteria <> "" then
	sFilter=" AND (" & filterfield & criteriaOperator
	select case filtertype
		case "string"
			sDelim = "'"
		
		case "numeric"
			'no delim because numbers don't have criteria wrappers in sql
		
		case "date"
			sDelim = "#"
		
		case else
			sDelim = "'"
		
	end select
	
	if instr(filtercriteria,",")>0 then
		arr_filtercriteria=split(filtercriteria,",")
		for x=0 to ubound(arr_filtercriteria)
			sFilter = sFilter & sDelim & trim(arr_filtercriteria(x)) & sDelim & ","
		next
		sFilter=left(sFilter,len(sFilter)-1)
	else
		sFilter = sFilter & sDelim & filtercriteria & sDelim
	end if
	
	sFilter = sFilter & criteriaOperatorClose & ")"
else
	sFilter = ""
end if

from=request.QueryString("from")
if from="" then 
	from=date()
else
	from=cdate(from)
end if
%>

<SCRIPT LANGUAGE="javascript">
var timer=60
window.setTimeout("fCountDown();",1000)
</SCRIPT>

<SCRIPT LANGUAGE="javascript">
function OpenTicket(ticket,contract)
{
user="<%= request.Cookies("Hsuser") %>"
url="accountticketframeset.asp?FromDispatch=yes&ticketid="+ticket+"&ContractID="+contract+"&user="+user
title="ticket_"+ticket

ticketwindow=window.open(url,title,"height=700,width=750,status=no")
}

function fCountDown()
{
cd=document.getElementById("countdown")
timer=timer-1
cd.innerHTML=timer + " seconds"
window.setTimeout("fCountDown();",1000)
}

function sortby(what){

	var filterfield="<%= filterfield %>";
	var filtercriteria="<%= filtercriteria %>";
	    filtercriteria.replace(",","+");
	var filtertype="<%= filtertype %>";
	var fromval="<%= request.QueryString("from") %>";
	var toval="<%= request.QueryString("to") %>";
	
	if ("<%= sortcol %>" == what) {
		var direction="+desc";
	}
	else{
		var direction="";
	}
	var sortval=what + direction
	var city="<%= request.QueryString("city") %>"
	window.location.href="dispatchreportdata.asp?filterfield=" + filterfield + "&filtercriteria=" + filtercriteria + "&from=" + fromval + "&filtertype=" + filtertype + "&to=" + toval + "&sort=" + sortval;
	}
</SCRIPT>

<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">

<STYLE TYPE="text/css">
<!--
td {cursor:hand;}
-->
</STYLE>
</HEAD>

<BODY>
<% 'output filters to screen %>
<span id=ticketcount CLASS="ResultsAddress" STYLE="color:#000099"></span>&nbsp;|&nbsp;
<SPAN class='ResultsAddress'>Filter: Date Range(<%= from %> - <%= request.QueryString("to") %> ) - Filter: Field(<%= filterfield %>) - Criteria(<%= filtercriteria %>) - Sorted By(<%= sortcol %>)&nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; Auto-Refresh in:&nbsp;</SPAN>
<SPAN id='countdown' name='countdown' class='ResultsAddress'>60 seconds</SPAN><BR/>
<TABLE width='910' border='0' bgcolor='#009900' cellspacing='1' cellpadding='0'>
	<TR class='DispatchHeaders' TITLE="Click On Any Column Header To Sort The Displayed Tickets According To That Column In An Ascending Order. Click The Same Column A 2nd Time To Sort The Displayed Tickets According To That Column In A Descending Order" bgcolor='#FFFFFF'>
    <TD width='53' height='20' onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'" onClick="sortby('ticket_id')">Ticket#</TD>
    <TD width='52' height='20' onClick="sortby('contract_id')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'">Cont ID</TD>
    <TD width='46' height='20' onClick="sortby('zip')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'">Zip</TD>
    <TD width='150' height='20' onClick="sortby('address')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'">Address</TD>
    <TD width='103' height='20' onClick="sortby('appliance')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'">Appliance</TD>
    <TD width='48' height='20' onClick="sortby('emergency')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'">Emerg.</TD>
    <TD width='125' height='20' onClick="sortby('created_time')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'">Created</TD>
    <TD width='125' height='20' onClick="sortby('dispatched_time')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'">Dispatched</TD>
    <TD width='72' height='20' onClick="sortby('status')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'"> Status</TD>
    <TD width='125' height='20' onClick="sortby('follow_up')" onMouseOver="this.bgColor='#CCFFCC'" onMouseOut="this.bgColor='#FFFFFF'">Follow-Up</TD>
  </TR>
<%
dim db,rs,colorDB,colorRS,AHdb,AHrs
set db=server.CreateObject("adodb.connection")
set rs=server.CreateObject("adodb.recordset")

FilePath=server.MapPath("/afterhours/access/backend/tickets.mdb")

db.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath)

'get city list
if city<>"" then 
	set AHdb=server.CreateObject("adodb.connection")
	set AHrs=server.CreateObject("adodb.recordset")
	AHfilepath=server.MapPath("/afterhours/access/backend/afterhours_be.mdb")
	AHdb.open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AHfilepath)
	AHsql="select zip from [colorado zipcodes] where city='" & city & "'"
	AHrs.open AHsql,AHdb,1,2
	
	if AHrs.recordcount=0 then
		citylist="-100"
	else
		AHra=AHrs.getrows()
		for x=0 to ubound(AHra,2)
			citylist=citylist & AHra(0,x) & ","
		next
		citylist=left(citylist,len(citylist)-1)
		
	end if
	
	AHrs.close
	AHdb.close
	set AHrs=nothing
	set AHdb=nothing

end if

qTo=request.QueryString("to")
if qTo="" then 
	qTo=dateadd("d",1,date())
else
	qTo=dateadd("d",1,cdate(qTo))
end if


'tickets for timeframe sorted by chosen sort column
sql="select * from CopyqryDispatch_TicketCust where (created_time between #" & from & "# and #" & qTo & "#)" & sFilter & " ORDER BY " & sortcol
'response.Write(sql)
'response.End()
rs.open sql,db,1,2

'create a string that will be the html table output
if Application("OpenOverAnHourColor")="" then
	filepathcolor=server.MapPath("/afterhours/access/backend/admin.mdb")
	set colorDB=server.CreateObject("adodb.connection")
	set colorRS=server.CreateObject("adodb.recordset")
	colorDB.open ("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & filepathcolor)
	colorRS.cursorlocation=3 'adUseClient
	colorRS.cursortype= 1 'adOpenKeyset
	colorRS.locktype= 3 'adLockOptimistic

	colorRS.open "select Status_Color from dispatchcolors where Status_Name = 'Open Over An Hour'",colorDB
	colorRS.activeconnection=nothing
	if colorRS.recordcount=0 then
		Application("OpenOverAnHourColor")="FF0000"
	else
		Application("OpenOverAnHourColor")=colorRS(0)
	end if
	colorRS.close
	colorDB.close
	set colorRS=nothing
	set colorDB=nothing
end if
	
	

open_over_an_hour_color=Application("OpenOverAnHourColor")
	
if rs.recordcount > 0 then 
	rs.movefirst
	do until rs.eof

		color=rs("status_color")
		if color="" then color="ffffff"
		
		
		contractID = iif(isnull(rs("contract_id")),"(No Contract ID)",rs("contract_id"))
		ticketID = iif(isnull(rs("ticket_id")),"(No Ticket ID)",rs("ticket_id"))
		custName = iif(isnull(rs("custname")),"(No Customer Name)",trim(rs("custname")))
		City = iif(isnull(rs("city")),"(No City)",trim(rs("city")))
		Zip = iif(isnull(rs("zip")),"(No Zip)",rs("zip"))
		Address = iif(isnull(rs("address")),"(No Address)",rs("address"))
		Appliance = iif(isnull(rs("appliance")),"(No Appliance)",rs("appliance"))
		Emergency = iif(isnull(rs("emergency")),"(Emergency State Not Defined)",rs("emergency"))
		CreatedTime = iif(isnull(rs("created_time")),"(No Created Time)",rs("created_time"))
		Stat = iif(isnull(rs("status")),"(No Status)",rs("status"))
		
		if (Stat="Open") and (cdate(rs("created_time"))<dateadd("h",-1,now())) then color=open_over_an_hour_color
		%>	
			<TR BGCOLOR="#<%= color %>" onMouseOver="this.bgColor='#ffff99'" onMouseOut="this.bgColor='#<%= color %>'" onClick="javascript: OpenTicket('<%= ticketID %>','<%= contractID %>')" CLASS="DispatchData" HEIGHT="20" TITLE="<%= custName & " - " & City %>">
			<TD><%= ticketID %></TD>
			<TD><%= contractID %></TD>
			<TD><%= Zip %></TD>
			<TD><%= Address %></TD>
			<TD><%= Appliance %></TD>
			<TD><%= Emergency %></TD>
			<TD><%= CreatedTime %></TD>
			<TD><%= rs("Given_To") & " " & rs("Dispatched_Time") %></TD>
			<TD><%= Stat %></TD>
			<TD><%= rs("follow_up") %></TD>
			</TR>
		<%
		rs.movenext
	loop
	ticketcount=rs.recordcount
else
%>
	<TR BGCOLOR="#FFFFFF"><TD COLSPAN="10" CLASS="TableTitles" >(None)</TD></TR>
<%
	ticketcount=0
end if
	
%>
<SCRIPT TYPE="TEXT/VBSCRIPT">
	<% 
	if ticketcount<>1 then 
		ticketcountstr=" Tickets"
	else
		ticketcountstr=" Ticket"
	end if
	%>
	document.getElementById("ticketcount").innerhtml="<%= ticketcount & ticketcountstr %>"
	
</script>
<%
if isobject("rs") then if rs.state=1 then rs.close
if isobject("db") then if db.state=1 then db.close

set rs=nothing
set db=nothing
%>
</TABLE>
</BODY>
</HTML>
